package com.common.jdbc;

import java.util.UUID;

import org.apache.log4j.Logger;

import com.common.util.StringUtil;


public class SqlUtil {
	
	private static Logger log = Logger.getLogger(SqlUtil.class);
	private static String currentDialect="oracle";
	
	public static String getAnalyseSQLOfcount(String sourceSQL) {
		String strSQL = "";
		sourceSQL = sourceSQL.replaceAll("FROM", "from");
		sourceSQL = sourceSQL.replaceAll("ORDER", "order");
		strSQL = "select   count(1)   as   COUNT ";
		strSQL += sourceSQL.substring(sourceSQL.indexOf("from"));
		if (strSQL.indexOf("order") != -1) {
			strSQL = strSQL.substring(0, strSQL.indexOf("order"));
		}
		log.info("=====count  sql========"+strSQL);
		return strSQL;
	}
	
	public static String db2ObjStr(String sql){
		if(sql.indexOf(".")!=-1){
			sql = sql.substring(sql.indexOf(".")+1);
		}
		sql = sql.toLowerCase();
		while(sql.indexOf("_")!=-1){
			sql = sql.substring(0,sql.indexOf("_"))
			+sql.substring(sql.indexOf("_")+1, sql.indexOf("_")+2).toUpperCase()
			+sql.substring(sql.indexOf("_")+2).toLowerCase();
		}
		return sql;
	}
	
	/**
	 * @param metaName ID
	 * @param ids '1,3,5,9,11'
	 * @return and ID in('1','3','5','9','11');
	 */
	public static String getInSql(String metaName,String ids){
		StringBuilder sql = new StringBuilder();
		if(!StringUtil.isBlank(ids)){
			String[] idArr = ids.split(",");
			sql.append(" and "+metaName+" in(");
			for(int i=0;i<idArr.length;i++){
				sql.append("'"+idArr[i]+"'");
				if(i!=(idArr.length-1)){
					sql.append(",");
				}
			}
			sql.append(")");
		}
		return sql.toString();
	}
	
	 public static String getSqlLimit(String _sql, boolean hasOffset, int _start, int _limit) {   
         
	        /**  
	         * #############Oracle分页方式###############  
	         */  
	        if(currentDialect.toLowerCase().indexOf("oracle") >= 0) {   
	            _sql = _sql.trim();   
	            boolean isForUpdate = false;   
	            if ( _sql.toLowerCase().endsWith(" for update") ) {   
	                _sql = _sql.substring( 0, _sql.length()-11 );   
	                isForUpdate = true;   
	            }   
	               
	            StringBuffer pagingSelect = new StringBuffer(_sql.length()+100 );   
	            if (hasOffset) {   
	                pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");   
	            }   
	            else {   
	                pagingSelect.append("select * from ( ");   
	            }   
	            pagingSelect.append(_sql);   
	            if (hasOffset) {   
	                pagingSelect.append(" ) row_ where rownum <= " + (_start + _limit) + ") where rownum_ > " + _start);   
	            }   
	            else {   
	                pagingSelect.append(" ) where rownum <= " + (_start + _limit));   
	            }   
	  
	            if (isForUpdate) pagingSelect.append(" for update");   
	               
	            return pagingSelect.toString();   
	        }
	        
	        /**  
	         * ############## HSQL方式 ###############  
	         */  
	        else if(currentDialect.toLowerCase().indexOf("hsql") >= 0) {   
	            return new StringBuffer( _sql.length() + 10 )   
	            .append( _sql )   
	            .insert( _sql.toLowerCase().indexOf( "select" ) + 6, hasOffset ? " limit " + _start + " " + _limit : " top "+_start)   
	            .toString();   
	        }   
	           
	        /**  
	         * ############## MYSQL分页方式 ################  
	         */ 
	        else if(currentDialect.toLowerCase().indexOf("mysql") >= 0) {   
	            return new StringBuffer(_sql.length()+20 )   
	            .append(_sql)   
	            .append( hasOffset ? " limit " + _start + ", " + _limit : " limit " + _limit)   
	            .toString();   
	        }    
	           
	        /**  
	         * ############## SQLServer分页方式 ################  
	         */  
	        else if(currentDialect.toLowerCase().indexOf("sqlserver") >= 0) {   
	            StringBuffer pagingBuilder = new StringBuffer();     
	            String orderby = getOrderByPart(_sql);     
	            String distinctStr = "";     
	  
	            String loweredString = _sql.toLowerCase();     
	            String sqlPartString = _sql.trim();     
	            if (loweredString.trim().startsWith("select")) {     
	                int index = 6;     
	                if (loweredString.startsWith("select distinct")) {     
	                    distinctStr = "DISTINCT ";     
	                    index = 15;     
	                }     
	                sqlPartString = sqlPartString.substring(index);     
	            }     
	            pagingBuilder.append(sqlPartString);     
	  
	            // if no ORDER BY is specified use fake ORDER BY field to avoid errors     
	            if (orderby == null || orderby.length() == 0) {     
	                orderby = "ORDER BY CURRENT_TIMESTAMP";     
	            }     
	  
	            StringBuffer result = new StringBuffer();     
	            result.append("SELECT * FROM (")      
	            .append("SELECT ")     
	            .append(distinctStr)     
	            .append(" TOP 100 PERCENT ROW_NUMBER() OVER (") //使用TOP 100 PERCENT可以提高性能     
	            .append(orderby)     
	            .append(") AS rownum_, ")     
	            .append(pagingBuilder)     
	            .append(") as ucstarTempTable WHERE rownum_ >")   
	            .append(_start)     
	            .append(" AND rownum_ <=")     
	            .append(_start + _limit)     
	            .append(" ORDER BY rownum_");     
	  
	            return result.toString();   
	        }   
	           
	        /**  
	         * ############# 不支持的分页 ##############  
	         */  
	        else {   
	            log.error("No support Paging!");   
	            return _sql;   
	        }   
	    }   
	       
	       
    /**  
     * SQLServer的处理  
     * polarbear 2009-5-9   
     * @param sql  
     * @return  
     */  
    static String getOrderByPart(String sql) {   
        String loweredString = sql.toLowerCase();   
        int orderByIndex = loweredString.indexOf("order by");   
        if (orderByIndex != -1) {   
            // if we find a new "order by" then we need to ignore   
            // the previous one since it was probably used for a subquery   
            return sql.substring(orderByIndex);   
        } else {   
            return "";   
        }   
    } 

	public static String getUuid(){
		return UUID.randomUUID().toString().replace("-", "");
	}
}
